﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

using System.Configuration;
using System.Text;
using BTAlertWinService.Models;
using BTAlertWinService.Logging;

namespace BTAlertWinService.DBOps
{
    public class BTAlertOperations
    {
        public static string connStrings = null;
        static BTAlertOperations()
        {
            connStrings = System.Configuration.ConfigurationManager.ConnectionStrings["AlertConnection"].ConnectionString;

        }
        public static IList<Applications> getApplications()
        {
            
            FileLogger.Log("getApplications has started.");

            //select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application            
            StringBuilder query = new StringBuilder();
            List<Applications> apps = null;
            query.Append("select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application");

            using (SqlConnection conn = new SqlConnection(connStrings))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(query.ToString(), conn);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
                apps = new List<Applications>();

                while (reader.Read())
                {
                    apps.Add(new Applications() { Description = Convert.ToString(reader["nvcDescription"]), nID = Convert.ToString(reader["nID"]), nvcName = Convert.ToString(reader["nvcName"]) });
                }
                conn.Close();
            }

            FileLogger.Log("getApplications has Successful.");

            return apps;
        }



        private static DataTable SndCreateDataTable(IEnumerable<ASendLoctions> locations)
        {
            DataTable table = new DataTable();
            table.Columns.Add("ApplicationName", typeof(string));
            table.Columns.Add("PortName", typeof(string));
            table.Columns.Add("LocationName", typeof(string));
            table.Columns.Add("disabled", typeof(string));
            table.Columns.Add("type", typeof(string));
            foreach (ASendLoctions location in locations)
            {
                table.Rows.Add(location.ApplicationName, location.PortName, location.LocationName, !location.PortDisabled,"Send");
            }
            return table;
        }

        private static DataTable RcvCreateDataTable(IEnumerable<AReceiveLocations> locations)
        {
            DataTable table = new DataTable();
            table.Columns.Add("ApplicationName",typeof(string));
            table.Columns.Add("PortName", typeof(string));
            table.Columns.Add("LocationName", typeof(string));
            table.Columns.Add("disabled", typeof(string));
            table.Columns.Add("type", typeof(string));
            foreach (AReceiveLocations location in locations)
            {
                table.Rows.Add(location.ApplicationName, location.PortName, location.LocationName, location.PortDisabled, "Receive");
            }
            return table;
        }

        public static void  InsertReceiveLocations(IEnumerable<AReceiveLocations> locations)
        {            
            FileLogger.Log("InsertReceiveLocations has started.");

            //select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application            
            StringBuilder query = new StringBuilder();
            List<AReceiveLocations> apps = null;

            using (SqlConnection conn = new SqlConnection(connStrings))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("InsertLocationMonitor", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@locations", RcvCreateDataTable(locations));

                cmd.ExecuteNonQuery();
                conn.Close();
            }
            FileLogger.Log("InsertReceiveLocations has Successful.");

        }

        public static void InsertSendLocations(IEnumerable<ASendLoctions> locations)
        {
            FileLogger.Log("InsertSendLocations has started.");

            //select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application            
            StringBuilder query = new StringBuilder();
            List<ASendLoctions> apps = null;

            using (SqlConnection conn = new SqlConnection(connStrings))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("InsertLocationMonitor", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@locations", SndCreateDataTable(locations));

                cmd.ExecuteNonQuery();
                conn.Close();
            }
            FileLogger.Log("InsertSendLocations has Successful.");

        }

        public static IList<AAlerts> fetchNewAlerts()
        {            
            FileLogger.Log("fetchNewAlerts has started.");
            //select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application            
            StringBuilder query = new StringBuilder();
            List<AAlerts> locations = null;
           
            using (SqlConnection conn = new SqlConnection(connStrings))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("fetchNewAlerts", conn);
                cmd.CommandType = CommandType.StoredProcedure;                
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
                locations = new List<AAlerts>();

                while (reader.Read())
                {
                    locations.Add(new AAlerts()
                    {
                        LocationName = Convert.ToString(reader["LocationName"]),
                        PrimaryEmail = Convert.ToString(reader["PrimaryEmail"]),
                        SecondaryEmail = Convert.ToString(reader["SecondaryEmail"]),
                        restartIfStopped = Convert.ToBoolean(reader["restartIfStopped"]),
                        email = Convert.ToBoolean(reader["email"]),
                        Others = Convert.ToString(reader["Others"]),
                        type= Convert.ToString(reader["Type"])
                    });
                }
                conn.Close();
            }
            FileLogger.Log("fetchNewAlerts has Successful.");
            return locations;
        }

        public static IList<downTimeReporting> fetchDownTime(int dtRange)
        {
            FileLogger.Log("fetchDownTime has started.");
            //select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application            
            StringBuilder query = new StringBuilder();
            List<downTimeReporting> locations = null;

            using (SqlConnection conn = new SqlConnection(connStrings))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("downTimeReport", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@dateRageInms", dtRange);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
                locations = new List<downTimeReporting>();
                while (reader.Read())
                {
                    locations.Add(new downTimeReporting()
                    {

                        DownTime = Convert.ToString( reader["DownTime"]), 
                    LocationName = Convert.ToString(reader["LocationName"])
                    });

                }
                conn.Close();
            }
            FileLogger.Log("fetchDownTime has Successful.");
            return locations;
        }
        public static int UpdateLocationMonitor(string LocationName, string Kind)
        {        
            FileLogger fileLog = new FileLogger();
            FileLogger.Log("UpdateLocationMonitor has started.");
            
            //select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application            
            StringBuilder query = new StringBuilder();            

            using (SqlConnection conn = new SqlConnection(connStrings))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("UpdateLocationMonitor", conn);
                cmd.Parameters.AddWithValue("@LocationName", LocationName);
                cmd.Parameters.AddWithValue("@Type", Kind);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
                
                conn.Close();
                
            }
            FileLogger.Log("UpdateLocationMonitor has Successful.");
            return 0;
            
        
        }
    }
}